# -*- coding: utf-8 -*-
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.page import PageMargins
from datetime import date, timedelta, datetime
import chinese_calendar as calendar
import random
import os
from collections import defaultdict

def get_workdays(year, month, max_trips):
    """获取有效工作日并随机打乱，数量与用户输入的最多出发次数一致"""
    try:
        start_date = date(year, month, 1)
        end_date = date(year, month+1, 1) - timedelta(days=1) if month != 12 else date(year+1, 1, 1) - timedelta(days=1)
    except ValueError as e:
        raise ValueError("无效的年份或月份") from e

    workdays = []
    current_date = start_date
    while current_date <= end_date:
        if calendar.is_workday(current_date):
            workdays.append(current_date)
        current_date += timedelta(days=1)
    
    if not workdays:
        raise ValueError("该月份无有效工作日")
    
    # 如果工作日数量超过最大出发次数，则随机选择相应数量的工作日
    if len(workdays) > max_trips:
        workdays = random.sample(workdays, max_trips)
    
    # 随机打乱工作日列表
    random.shuffle(workdays)
    
    # 按照时间顺序排序
    workdays.sort()
    
    return workdays

def read_area_data():
    """读取目的地数据"""
    file_path = "目的地.xlsx"
    
    if not os.path.exists(file_path):
        raise FileNotFoundError(f"文件 {os.path.abspath(file_path)} 不存在")

    try:
        wb = load_workbook(file_path)
        ws = wb.active
        
        # 验证列结构
        header = [cell.value for cell in ws[1]]
        required_columns = ["序号", "区县", "目的地", "里程(距离市局，KM)"]
        if len(header) < 4 or not all(h in str(col) for h, col in zip(required_columns, header)):
            raise ValueError("文件列标题必须包含：序号、区县、目的地、里程(距离市局，KM)")

        data = []
        for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
            if len(row) < 4:
                print(f"警告：第{row_idx}行数据不完整，已跳过")
                continue
            
            try:
                mileage = float(row[3])
                if mileage <= 0:
                    raise ValueError("里程必须大于0")
                
                item = {
                    "区县": str(row[1]).strip(),
                    "目的地": str(row[2]).strip(),
                    "里程": mileage * 2  # 强制乘以2
                }
                data.append(item)
            except Exception as e:
                raise ValueError(f"第{row_idx}行数据格式错误：{str(e)}")

        if not data:
            raise ValueError("文件中没有有效数据")
        return data
    
    except Exception as e:
        raise RuntimeError(f"读取文件失败：{str(e)}") from e

def read_affairs_data():
    """读取公务事项数据"""
    file_path = "公务事项.xlsx"
    
    if not os.path.exists(file_path):
        raise FileNotFoundError(f"文件 {os.path.abspath(file_path)} 不存在")

    try:
        wb = load_workbook(file_path)
        ws = wb.active
        affairs = []
        
        # 验证列结构
        header = [cell.value for cell in ws[1]]
        if len(header) < 2 or header[0] != "序号" or header[1] != "公务事项":
            raise ValueError("文件列标题必须为：序号、公务事项")
        
        # 读取第二列数据
        for row in ws.iter_rows(min_row=2, values_only=True):
            if len(row) >= 2 and row[1]:
                affairs.append(str(row[1]).strip())
        
        if not affairs:
            raise ValueError("文件中没有有效公务事项")
        return affairs
    
    except Exception as e:
        raise RuntimeError(f"读取公务事项失败：{str(e)}") from e

def create_travel_sheet(yyyymm, average_fuel_consumption, fuel_price, max_total, max_trips=12, float_range=4):
    """生成公务出行登记表
    :param max_trips: 最大出发次数，默认12
    :param float_range: 浮动范围百分比，默认4%
    """
    try:
        # 解析年份和月份
        year = int(yyyymm[:4])
        month = int(yyyymm[4:])
        
        # 获取工作日
        workdays = get_workdays(year, month, max_trips)
        
        # 计算允许的金额范围
        max_total_allowed = max_total * (1 + float_range/100)
        min_total = max_total * (1 - float_range/100)
        
        # 计算单价
        unit_price = average_fuel_consumption * fuel_price / 100
        
        # 读取数据
        area_data = read_area_data()
        affairs_list = read_affairs_data()
        
        # 目的地轮询机制
        random.shuffle(area_data)
        destination_pool = area_data.copy()
        
        # 保留公务事项轮询机制
        affairs_counter = defaultdict(int)
        
        # 初始化其他变量
        total_reimbursement = 0.0
        used_days = 0
        used_destinations = []
        all_records = []

        # 生成主逻辑
        while used_days < max_trips and workdays and total_reimbursement < max_total_allowed:
            # 优先使用未使用过的目的地
            if not destination_pool:
                destination_pool = area_data.copy()
                random.shuffle(destination_pool)
            
            data_item = destination_pool.pop()
            
            # 获取日期（确保工作日）
            if not workdays:
                break
            day = workdays.pop(0)
            
            # 使用轮询机制选择公务事项
            affair = min(affairs_list, key=lambda x: affairs_counter[x])
            affairs_counter[affair] += 1
            
            # 计算里程和金额（四舍五入为整数）
            mileage = int(round(data_item["里程"]))
            reimbursement = int(round(mileage * unit_price))
            
            # 金额控制
            if total_reimbursement + reimbursement > max_total_allowed:
                allowable = max_total_allowed - total_reimbursement
                if allowable <= 0:
                    break
                reimbursement = int(round(allowable))
                mileage = int(round(reimbursement / unit_price))
            
            # 生成记录 - 修改公务事项的格式，科室负责人确认列设为空
            record = [
                used_days + 1,
                day.strftime(f"{day.month}月{day.day}日"),
                f"前往{data_item['区县']}开展{affair}工作",  # 使用从公务事项.xlsx中读取的内容
                "市局",
                data_item["目的地"],
                "自驾",
                mileage,
                "加油发票",
                reimbursement,
                ""  # 科室负责人确认列设为空
            ]
            
            all_records.append(record)
            total_reimbursement += reimbursement
            used_days += 1
            used_destinations.append(data_item["目的地"])
            
            # 达到最小限额时检查
            if total_reimbursement >= min_total:
                break

        # 金额范围校验
        if not (min_total <= total_reimbursement <= max_total_allowed):
            print(f"\n⚠️ 报销金额 {total_reimbursement} 元不符合要求（范围：{min_total:.0f} ~ {max_total_allowed:.0f} 元），正在重新生成...")
            return create_travel_sheet(yyyymm, average_fuel_consumption, fuel_price, max_total, max_trips, float_range)

        # ==== 生成Excel文件 ====
        filename = f"事业人员公务出行登记确认表{year:04d}{month:02d}_{random.randint(1000,9999)}.xlsx"
        wb = Workbook()
        ws = wb.active
        ws.title = "Sheet1"

        # 打印设置
        ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
        ws.page_setup.paperSize = ws.PAPERSIZE_A4
        ws.page_margins = PageMargins(left=0.5, right=0.5, top=0.5, bottom=0.5)
        
        # 设置列宽
        column_widths = [6, 10, 35, 12, 15, 10, 8, 15, 10, 15]
        for i, width in enumerate(column_widths, 1):
            ws.column_dimensions[get_column_letter(i)].width = width

        # 标题行
        ws.merge_cells('A1:J1')
        ws['A1'] = "事业人员公务出行登记确认表"
        ws['A1'].font = Font(name='宋体', size=16, bold=True)
        ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

        # 添加科室、姓名和时间行
        ws.merge_cells('A2:B2')
        ws['A2'] = "科室："
        ws['A2'].alignment = Alignment(horizontal='right', vertical='center')
        
        ws.merge_cells('D2:E2')
        ws['D2'] = "姓名："
        ws['D2'].alignment = Alignment(horizontal='right', vertical='center')
        
        ws.merge_cells('I2:J2')
        ws['I2'] = f"时间：    {year}年{month}月"
        ws['I2'].alignment = Alignment(horizontal='right', vertical='center')

        # 表头
        headers = ["序号", "日期", "公务事项", "出发地", "目的地", "出行方式", "里程\n(公里)", "报销凭证类型", "报销金额", "科室负责人确认"]
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=3, column=col)
            cell.value = header
            cell.font = Font(name='宋体', size=11)
            cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
            cell.border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )

        # 写入数据
        for row_idx, record in enumerate(all_records, 4):
            for col_idx, value in enumerate(record, 1):
                cell = ws.cell(row=row_idx, column=col_idx)
                cell.value = value
                cell.font = Font(name='宋体', size=11)
                cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
                cell.border = Border(
                    left=Side(style='thin'),
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin')
                )

        # 合计行
        total_row = ws.max_row + 1
        total_mileage = sum(r[6] for r in all_records)
        ws.merge_cells(f'A{total_row}:F{total_row}')
        ws[f'A{total_row}'].value = "合计"
        ws[f'A{total_row}'].alignment = Alignment(horizontal='center', vertical='center')
        
        ws[f'G{total_row}'].value = total_mileage
        ws[f'H{total_row}'].value = ""
        ws[f'I{total_row}'].value = total_reimbursement
        ws[f'J{total_row}'].value = ""

        # 为合计行添加边框和字体
        for col in range(1, 11):
            cell = ws.cell(row=total_row, column=col)
            cell.font = Font(name='宋体', size=11)
            cell.border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
            cell.alignment = Alignment(horizontal='center', vertical='center')

        # 添加备注信息
        remarks = [
            "备注：",
            "1、公交车、出租车出行，须填写报销凭证类型，报销金额根据发票金额据实填写。",
            "2、网约车出行，须在平台上开具电子发票及行程单，报销金额根据发票金额据实填写。",
            "3、自驾车出行，须记录里程数（可使用高德地图、百度地图等软件查询），根据油耗情况，填写出行费用报销金额。"
        ]
        
        start_row = total_row + 1
        for i, remark in enumerate(remarks):
            cell = ws.cell(row=start_row + i, column=1)
            ws.merge_cells(f'A{start_row + i}:J{start_row + i}')
            cell.value = remark
            cell.font = Font(name='宋体', size=11)
            cell.alignment = Alignment(horizontal='left', vertical='center')

        # 调整行高
        ws.row_dimensions[1].height = 30  # 标题行高
        ws.row_dimensions[2].height = 25  # 信息行高
        ws.row_dimensions[3].height = 30  # 表头行高
        for row in range(4, total_row + 1):
            ws.row_dimensions[row].height = 25  # 数据行高
        
        # 为整个表格添加外边框
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=10):
            for cell in row:
                if not cell.border:
                    cell.border = thin_border

        # 保存文件
        wb.save(filename)
        print(f"\n✅ 文件已生成：{os.path.abspath(filename)}")
        print(f"▸ 总记录数：{used_days}条")
        print(f"▸ 总里程：{total_mileage}公里")
        print(f"▸ 总报销金额：{total_reimbursement}元（限额：{max_total}元 ±{float_range}%）")

    except Exception as e:
        print(f"\n❌ 发生错误：{str(e)}")

def get_user_input():
    """获取用户输入"""
    print("""\
============================================
      公务出行登记表生成工具
============================================
请按顺序输入以下参数（用英文逗号分隔）：
1. 年份和月份（格式：YYYYMM，如：202406）
2. 平均油耗（升/百公里，如：15）
3. 汽油单价（元/升，如：7.6）
4. 报销限额（元，如：500）
5. 出发次数（可选，默认12，如：10）
6. 浮动范围%（可选，默认4%，如：5%）
示例输入：202406,15,7.6,500,10,4%
============================================
""")
    
    while True:
        try:
            inputs = input("请输入参数：").strip().replace('%','').split(',')
            if len(inputs) < 4:
                raise ValueError("输入参数不足")
                
            # 解析必填参数
            yyyymm = inputs[0]
            if len(yyyymm) != 6 or not yyyymm.isdigit():
                raise ValueError("年份和月份格式应为YYYYMM")
            avg_fuel = float(inputs[1])
            fuel_price = float(inputs[2])
            limit = int(inputs[3])
            
            # 解析可选参数
            max_trips = 12  # 默认值
            if len(inputs) >= 5 and inputs[4]:
                max_trips = int(inputs[4])
                if max_trips <= 0:
                    raise ValueError("出发次数必须大于0")
            
            float_range = 4  # 默认值
            if len(inputs) >= 6 and inputs[5]:
                float_range = float(inputs[5])
                if not 0 < float_range <= 20:
                    raise ValueError("浮动范围应在0-20%之间")
            
            # 验证基本输入
            if not (1 <= int(yyyymm[4:]) <= 12):
                raise ValueError("月份应为1-12")
            if avg_fuel <= 0 or fuel_price <=0 or limit <=0:
                raise ValueError("油耗、油价、限额必须大于0")
            
            return (yyyymm, avg_fuel, fuel_price, limit, max_trips, float_range)
            
        except Exception as e:
            print(f"输入错误：{str(e)}，请重新输入\n")

if __name__ == "__main__":
    try:
        # 获取用户输入
        params = get_user_input()
        # 调用生成函数
        create_travel_sheet(*params)
    except KeyboardInterrupt:
        print("\n操作已取消")
    except Exception as e:
        print(f"程序运行出错：{str(e)}")
